<?php

class Customer extends AppModel {

    var $name = 'Customer';
    var $displayField = 'name';
    var $order = 'Customer.name';
    var $belongsTo = array(
        'User' => array(
            'foreignKey' => 'collector_id',
            'fields' => array('username', 'name')
        )
    );
    var $hasMany = array(
        'Site', 'CustomerSupplier'
    );
    var $validate = array(
        'name' => array(
            'notEmpty' => array(
                'rule' => array('notEmpty'),
                'message' => 'Please input a customer name.',
                'required' => true,
            ),
            'isUnique' => array(
                'rule' => 'isUnique',
                'message' => 'This customer name is already taken.',
            ),
        ),
    );

    /**
     * ถูกเรียกมาจากหน้า home ของ ITS, Vendor
     * @param array $customers list customer ที่จะแสดง
     * @param string $dispatch_from
     * @return array
     */
    function findCustStockAlert($customers, $dispatch_from, $supplier_id = null) {
        $sql = 'SELECT *, cust_name IS NULL cust_null, site_name IS NULL site_null
            FROM (
            SELECT Customer.id customer_id, Customer.name cust_name, Site.id site_id, Site.name site_name, SUM(current_stock) current_stock, SUM(reserve_qty) reserve_qty,
                    SUM(CASE WHEN CustProductSite.current_stock <= CustProductSite.min_stock THEN 1 ELSE 0 END) num_red,
                    SUM(CASE WHEN CustProductSite.current_stock > CustProductSite.min_stock AND CustProductSite.current_stock < CustProductSite.max_stock THEN 1 ELSE 0 END) num_yellow,
                    SUM(CASE WHEN CustProductSite.current_stock > CustProductSite.max_stock THEN 1 ELSE 0 END) num_green
            FROM pre_customers Customer
            JOIN pre_sites Site ON (Customer.id = Site.customer_id)
            JOIN pre_cust_products_sites CustProductSite ON (Site.id = CustProductSite.site_id)
            WHERE Customer.id IN (' . implode(', ', $customers) . ')
            AND CustProductSite.min_stock IS NOT NULL
            AND CustProductSite.max_stock IS NOT NULL
            AND CustProductSite.current_stock IS NOT NULL
            AND CustProductSite.discontinued = 0
            AND CustProductSite.dispatch_from = "' . $dispatch_from . '" ';
        if ($supplier_id != null) {
            $sql .= ' AND CustProductSite.supplier_id = "' . $supplier_id . '" ';
        }
        $sql .= '
            GROUP BY Customer.name, Site.name WITH ROLLUP
            ) Row
            ORDER BY cust_null ASC, cust_name ASC, site_null DESC, site_name ASC ;';
        $data = $this->query($sql);
        return $data;
    }

    /**
     * ถูกเรียกมาจากหน้า home ของ Customer
     * @param array $customers list customer ที่จะแสดง
     * @param int $supplier_id ถ้า supplier view เข้ามาดู ส่ง supplier id นั้นเข้ามาด้วย
     * @return array
     */
    function findSupplierStockAlert($site_id, $supplier_id = null) {
        $sql = 'SELECT *, supplier_name IS NULL supplier_null, brand_name IS NULL brand_null
            FROM (
            SELECT dispatch_from, Supplier.id supplier_id,
			CASE WHEN dispatch_from = "its" THEN "its" ELSE Supplier.name END supplier_name,
			Brand.id brand_id, Brand.name brand_name, Customer.id customer_id, Customer.name cust_name, Site.id site_id, Site.name site_name, SUM(CustProductSite.current_stock) current_stock, SUM(reserve_qty) reserve_qty,
                    SUM(CASE WHEN CustProductSite.current_stock <= CustProductSite.min_stock THEN 1 ELSE 0 END) num_red,
                    SUM(CASE WHEN CustProductSite.current_stock > CustProductSite.min_stock AND CustProductSite.current_stock < CustProductSite.max_stock THEN 1 ELSE 0 END) num_yellow,
                    SUM(CASE WHEN CustProductSite.current_stock > CustProductSite.max_stock THEN 1 ELSE 0 END) num_green
            FROM pre_customers Customer
            JOIN pre_sites Site ON (Customer.id = Site.customer_id)
            JOIN pre_cust_products_sites CustProductSite ON (Site.id = CustProductSite.site_id)
            JOIN pre_products Product ON (CustProductSite.product_id = Product.id)
            JOIN pre_suppliers Supplier ON (Product.supplier_id = Supplier.id)
	    JOIN pre_brands Brand ON (Product.brand_id = Brand.id)
            LEFT JOIN pre_customers_suppliers CustomerSupplier ON ((CustProductSite.customer_id = CustomerSupplier.customer_id AND CustProductSite.supplier_id = CustomerSupplier.supplier_id AND CustomerSupplier.has_cust_inventory = 1))
            WHERE Site.id = ' . $site_id . '
            AND CustProductSite.min_stock IS NOT NULL
            AND CustProductSite.max_stock IS NOT NULL
            AND CustProductSite.current_stock IS NOT NULL
            AND CustProductSite.discontinued = 0 ' .
                ($supplier_id != null ? ' AND CustProductSite.supplier_id = ' . $supplier_id : ' ') .
                ' GROUP BY supplier_name, Brand.name WITH ROLLUP
            ) ROW
            ORDER BY supplier_null ASC, supplier_name ASC, brand_null DESC, brand_name ASC ;';
        $data = $this->query($sql);
        return $data;
    }

}

?>